package com.yc.jdbc;

import java.sql.*;

public class Demo1 {
	
	// 数据库事务场景: 订单 + 订单明细
	
	
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		selectDeptByDname1("SALES");
		System.out.println("---------------------------------");
		selectDeptByDname1("RESEARCH");
		System.out.println("---------------------------------");
		selectDeptByDname1("' or '1'='1");  // SQL 注入攻击
		System.out.println("---------------------------------");
		
//		insertDept(41, "研发部", "衡阳");
//		selectDeptByDname1("研发部");
		
		addDept("人事部","长沙");
		selectDeptByDname1("人事部");
		System.out.println("---------------------------------");
	}

	
	public static void addDept(String dname, String loc) {
		// 用数据库的序列产生编号  seq_dept
		// 1. 使用序列生成主键
		// 2. 使用事务控制提交和回滚
		// 业务场景: 每一次新增部门, 自动新一个该部门的员工, job为经理, 名字为"待入职"
		
		Connection conn = null;
		try {
			// 加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 获取连接
			String url = "jdbc:oracle:thin:@localhost:1521:orcl";
			String username = "scott";
			String password = "a";
			conn = DriverManager.getConnection(url, username, password);
			
			// 禁止自动提交, 会破坏事务性
			conn.setAutoCommit(false);
			
			////////////////////////////////////////////////////////////////////////
			String sql1 = "insert into dept values(seq_dept.nextval,?,?)";
			String sql2 = "insert into emp (empno,ename,job,deptno) values(seq_emp.nextval,?,?,?)";
			
			PreparedStatement ps1 = conn.prepareStatement(sql1, new String[] {"DEPTNO"});
			PreparedStatement ps2 = conn.prepareStatement(sql2);
			
			ps1.setString(1, dname);
			ps1.setString(2, loc);
			ps1.executeUpdate();
			// 如何知道当前新增的部门编号? select max(deptno) from dept 错误(多线程, 多任务)
			// 获取部门编号 序列值
			ResultSet rs = ps1.getGeneratedKeys();
			rs.next();
			int deptno = rs.getInt(1);
			
			ps2.setString(1, "待入职");
			ps2.setString(2, "MANAGER");
			ps2.setInt(3, deptno);
			ps2.executeUpdate();
			
			// 最后执行提交
			conn.commit();
			
		} catch (Exception e) {
			// 如果出现异常,则回滚
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException("数据库回滚错误：" + e1.getMessage());
			}
			throw new RuntimeException("数据库操作失败!",e);
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("数据库链接关闭错误：" + e.getMessage());
			}
		}
	}

	
	
	// 新增部门
	public static void insertDept(int deptno, String dname, String loc) {
		Connection conn = null;
		try {
			// 加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 获取连接
			String url = "jdbc:oracle:thin:@localhost:1521:orcl";
			String username = "scott";
			String password = "a";
			conn = DriverManager.getConnection(url, username, password);
			String sql = "insert into dept values (?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, deptno);
			ps.setString(2, dname);
			ps.setObject(3, loc);
			int rows = ps.executeUpdate();
			System.out.printf("新增了%s个部门！\n", rows);
		} catch (ClassNotFoundException e) {
			System.out.println("类没有找到：" + e.getMessage());
		} catch (SQLException e) {
			System.out.println("SQL错误：" + e.getMessage());
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException("数据库链接关闭错误：" + e.getMessage());
			}
		}
	}
	
	// 使用预编译语句查询
	public static void selectDeptByDname1(String inputDname)
			throws ClassNotFoundException, SQLException {
		// 加载驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		// 获取连接
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String username = "scott";
		String password = "a";
		Connection conn = DriverManager.getConnection(url, username, password);
		// 构建预编译语句 没有安全问题 => 防止注入攻击
		// 使用问号占位符 ？替代参数
		String sql = "select * from dept where dname = ?";
		// 高效 and 安全
		PreparedStatement ps = conn.prepareStatement(sql);
		// 设置参数
		ps.setString(1, inputDname);
		// 执行语句    ps.execute(sql); 从普通语句类继承过来的方法， 不要使用
		ResultSet rs = ps.executeQuery(); 
		// rs.next 返回是否有下一行数据
		while(rs.next()) {
			// 获取数据, 字段的序号从1开始
			int deptno = rs.getInt(1); //通过字段序号获取字段值
			String dname = rs.getString(2);
			String loc = rs.getString("LOC"); // 通过字段名获取字段值(推荐)
			System.out.printf("%4s %20s %20s \n", deptno, dname, loc);
		}
		// 关闭资源
		rs.close();
		ps.close();
		conn.close(); // 关闭上级对象, 那么下级对象也会一同关闭
	}
	
	
	/**
	 * 根据部门名称查询部门
	 * @param inputDname
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static void selectDeptByDname(String inputDname) throws ClassNotFoundException, SQLException {
		// 加载驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		// 获取连接
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String username = "scott";
		String password = "a";
		Connection conn = DriverManager.getConnection(url, username, password);
		// 构建普通语句 安全问题 => 注入攻击的风险
		Statement stat = conn.createStatement();
		// 执行语句
		ResultSet rs = stat.executeQuery("select * from dept where dname = '"+inputDname+"'");
		// rs.next 返回是否有下一行数据
		while(rs.next()) {
			// 获取数据, 字段的序号从1开始
			int deptno = rs.getInt(1); //通过字段序号获取字段值
			String dname = rs.getString(2);
			String loc = rs.getString("LOC"); // 通过字段名获取字段值(推荐)
			System.out.printf("%4s %20s %20s \n", deptno, dname, loc);
		}
		
		// 关闭资源
		rs.close();
		stat.close();
		conn.close(); // 关闭上级对象, 那么下级对象也会一同关闭
	}

}
